How to: Create custom filtering criteria.
Solution:
Select 'Filter' from the 'Data' menu and select 'AutoFilter'. Click the down arrow button in the column where filtering criteria should be entered. Select '(Custom...') from the drop-down list and specify the desired criteria in the 'Custom Filter' dialog box.
NOTE: Create custom filtering criteria to filter a list using one or two comparison criteria for the same column. To allow custom filtering criteria to work, the list must have column labels.
1) Select any cell in a list.
2) Select the 'Data' menu and select 'Filter'. (A submenu appears.)
the Filter submenu
3) Select 'AutoFilter' from the submenu. (The cells at the top row of the list become drop-down lists.)
NOTE: The 'AutoFilter' command in the submenu should not have a check mark on the left. If it does, select 'AutoFilter' to clear it first. Then start from Step 2) again.
4) Click the down arrow in the column where filtering criteria should be typed. (A drop-down list appears.)
the filtering drop-down lists
5) Select '(Custom...)' from the drop-down list. (The Custom AutoFilter dialog box appears.)
6) Do one of the following in the 'Show Rows Where' group:
a) Match only one criterion:
b) Match two criteria:
1] Select the desired comparison operator from the first operator drop-down list box on the left.
Selecting the first comparison operator
2] Type the first value in the first drop-down list box on the right.
Typing the first comparison value
3] Select the 'Add' or the 'Or' radio button.
4] Select the desired comparison operator from the second operator drop-down list box on the left.
5] Type the second value in the second drop-down list on the right.
6] Click 'OK'.
EXAMPLE: The criteria '>100' and '<500' will display only the rows that contain values greater than 100 but less than 500.
